using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using ReportToolAPI.Models;
using ReportToolAPI.Services;

namespace ReportToolAPI.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class ReportController : ControllerBase
    {
        private readonly DatabaseConfigService _configService;

        public ReportController(DatabaseConfigService configService)
        {
            _configService = configService;
        }

        /// <summary>
        /// 供应商档案查询
        /// </summary>
        [HttpPost("supplier-profile")]
        public async Task<IActionResult> SupplierProfile([FromBody] SupplierProfileRequest request)
        {
            try
            {
                // 获取数据库配置
                var dbConfig = _configService.GetById(request.DatabaseId);
                if (dbConfig == null)
                {
                    return Ok(ApiResponse<List<SupplierProfileResult>>.Fail("数据库配置不存在", 404));
                }

                // 构建连接字符串
                var connectionString = DatabaseTestService.BuildConnectionString(dbConfig);

                var results = new List<SupplierProfileResult>();

                using (var connection = new SqlConnection(connectionString))
                {
                    await connection.OpenAsync();

                    // 构建SQL查询语句
                    var sql = @"
                        SELECT 
                            LTRIM(RTRIM([Extent1].[supcust_no])) AS [编码], 
                            RTRIM([Extent1].[sup_name]) AS [供应商名称], 
                            RTRIM([Extent1].[sup_acct_back]) AS [开户行],
                            RTRIM([Extent1].[sup_acct_no]) AS [银行账号],
                            RTRIM([Extent2].[code_name]) AS [区域],  
                            RTRIM([Extent1].[other3]) AS [备注信息], 
                            RTRIM([Extent1].[sale_way]) AS [经营方式],
                            RTRIM([Extent1].[sup_man]) AS [联系人], 
                            RTRIM([Extent1].[mobile]) AS [手机], 
                            RTRIM([Extent1].[sup_tel]) AS [电话], 
                            [Extent1].[sup_ly_rate] AS [默认扣率], 
                            RTRIM([Extent1].[sup_addr]) AS [地址], 
                            RTRIM([Extent1].[sup_email]) AS [邮箱], 
                            [Extent1].[sup_ly_amt] AS [保底金额], 
                            RTRIM([Extent1].[other2]) AS [付款方式], 
                            RTRIM([Extent1].[status]) AS [状态], 
                            RTRIM([Extent1].[shopcard]) AS [营业执照号], 
                            RTRIM([Extent1].[sup_tax_no]) AS [税务登记号]
                        FROM [dbo].[t_bd_supcust_info] AS [Extent1]
                        LEFT OUTER JOIN [dbo].[t_bd_base_code] AS [Extent2] ON ('RG' = [Extent2].[type_no]) AND ([Extent1].[region_no] = [Extent2].[code_id])
                        LEFT OUTER JOIN [dbo].[t_bd_base_code] AS [Extent3] ON ('CT' = [Extent3].[type_no]) AND ([Extent1].[sup_type] = [Extent3].[code_id])
                        WHERE (N'S' = (UPPER([Extent1].[supcust_flag]))) 
                            AND (NOT ((N'3' = (CASE WHEN ([Extent1].[status] IS NULL) THEN N'0' ELSE [Extent1].[status] END)) 
                            AND (CASE WHEN ([Extent1].[status] IS NULL) THEN N'0' ELSE [Extent1].[status] END IS NOT NULL))) 
                            AND (N'' = (CASE WHEN ([Extent1].[parent_id] IS NULL) THEN N'' ELSE [Extent1].[parent_id] END))";

                    // 如果有关键词，添加模糊查询条件
                    if (!string.IsNullOrWhiteSpace(request.Keyword))
                    {
                        sql += " AND ([Extent1].[supcust_no] LIKE @Keyword OR [Extent1].[sup_name] LIKE @Keyword)";
                    }

                    using (var command = new SqlCommand(sql, connection))
                    {
                        // 添加参数
                        if (!string.IsNullOrWhiteSpace(request.Keyword))
                        {
                            command.Parameters.AddWithValue("@Keyword", $"%{request.Keyword}%");
                        }

                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                results.Add(new SupplierProfileResult
                                {
                                    Code = reader["编码"]?.ToString() ?? "",
                                    Name = reader["供应商名称"]?.ToString() ?? "",
                                    Bank = reader["开户行"]?.ToString() ?? "",
                                    BankAccount = reader["银行账号"]?.ToString() ?? "",
                                    Region = reader["区域"]?.ToString() ?? "",
                                    Remark = reader["备注信息"]?.ToString() ?? "",
                                    BusinessMode = reader["经营方式"]?.ToString() ?? "",
                                    Contact = reader["联系人"]?.ToString() ?? "",
                                    Mobile = reader["手机"]?.ToString() ?? "",
                                    Phone = reader["电话"]?.ToString() ?? "",
                                    DefaultRate = reader["默认扣率"] != DBNull.Value 
                                        ? Convert.ToDecimal(reader["默认扣率"]) : 0,
                                    Address = reader["地址"]?.ToString() ?? "",
                                    Email = reader["邮箱"]?.ToString() ?? "",
                                    MinAmount = reader["保底金额"] != DBNull.Value 
                                        ? Convert.ToDecimal(reader["保底金额"]) : 0,
                                    PaymentMethod = reader["付款方式"]?.ToString() ?? "",
                                    Status = reader["状态"]?.ToString() ?? "",
                                    BusinessLicense = reader["营业执照号"]?.ToString() ?? "",
                                    TaxNo = reader["税务登记号"]?.ToString() ?? ""
                                });
                            }
                        }
                    }
                }

                Console.WriteLine($"✓ 供应商档案查询成功，共 {results.Count} 条数据");
                return Ok(ApiResponse<List<SupplierProfileResult>>.Ok(results, $"查询成功，共 {results.Count} 条数据"));
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"❌ SQL查询失败: {ex.Message}");
                return Ok(ApiResponse<List<SupplierProfileResult>>.Fail($"SQL查询失败: {ex.Message}"));
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ 查询异常: {ex.Message}");
                return Ok(ApiResponse<List<SupplierProfileResult>>.Fail($"查询异常: {ex.Message}"));
            }
        }

        /// <summary>
        /// 合同费用明细查询
        /// </summary>
        [HttpPost("contract-expense")]
        public async Task<IActionResult> ContractExpense([FromBody] ContractExpenseRequest request)
        {
            try
            {
                var dbConfig = _configService.GetById(request.DatabaseId);
                if (dbConfig == null)
                {
                    return Ok(ApiResponse<List<ContractExpenseResult>>.Fail("数据库配置不存在", 404));
                }

                var connectionString = DatabaseTestService.BuildConnectionString(dbConfig);
                var results = new List<ContractExpenseResult>();

                using (var connection = new SqlConnection(connectionString))
                {
                    await connection.OpenAsync();

                    var sql = @"
                        SELECT 
                            b.supcust_no as 供应商编号,
                            b.sup_name as 供应商名称,
                            a.con_no as 合同号,
                            c.charge_name as 费用名,
                            a.chargevalue as 费用值,
                            a.charge_date as 扣款日期,
                            CASE 
                                WHEN a.settletype = 0 THEN '一次'
                                WHEN a.settletype = 1 THEN '每月'
                                WHEN a.settletype = 2 THEN '每年'
                                WHEN a.settletype = 3 THEN '周期'
                                ELSE '未知'
                            END as 结算周期
                        FROM 
                            t_bd_contract_charge a
                        JOIN 
                            t_bd_contract_master b ON a.con_no = b.con_no
                        JOIN 
                            t_bd_charge_info c ON a.chargeno = c.charge_no
                        WHERE 1=1";

                    if (!string.IsNullOrWhiteSpace(request.Keyword))
                    {
                        sql += " AND (b.supcust_no LIKE @Keyword OR b.sup_name LIKE @Keyword)";
                    }

                    sql += " ORDER BY a.charge_date DESC";

                    using (var command = new SqlCommand(sql, connection))
                    {
                        if (!string.IsNullOrWhiteSpace(request.Keyword))
                        {
                            command.Parameters.AddWithValue("@Keyword", $"%{request.Keyword}%");
                        }

                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                results.Add(new ContractExpenseResult
                                {
                                    SupplierCode = reader["供应商编号"]?.ToString() ?? "",
                                    SupplierName = reader["供应商名称"]?.ToString() ?? "",
                                    ContractNo = reader["合同号"]?.ToString() ?? "",
                                    ChargeName = reader["费用名"]?.ToString() ?? "",
                                    ChargeValue = reader["费用值"] != DBNull.Value 
                                        ? Convert.ToDecimal(reader["费用值"]) : 0,
                                    ChargeDate = reader["扣款日期"] != DBNull.Value 
                                        ? Convert.ToDateTime(reader["扣款日期"]) : DateTime.MinValue,
                                    SettlementCycle = reader["结算周期"]?.ToString() ?? ""
                                });
                            }
                        }
                    }
                }
                Console.WriteLine($"✓ 合同费用明细查询成功，共 {results.Count} 条数据");
                return Ok(ApiResponse<List<ContractExpenseResult>>.Ok(results, $"查询成功，共 {results.Count} 条数据"));
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"❌ SQL查询失败: {ex.Message}");
                return Ok(ApiResponse<List<ContractExpenseResult>>.Fail($"SQL查询失败: {ex.Message}"));
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ 查询异常: {ex.Message}");
                return Ok(ApiResponse<List<ContractExpenseResult>>.Fail($"查询异常: {ex.Message}"));
            }
        }

        /// <summary>
        /// 客单分析查询
        /// </summary>
        [HttpPost("customer-analysis")]
        public async Task<IActionResult> CustomerAnalysis([FromBody] CustomerAnalysisRequest request)
        {
            try
            {
                // 获取数据库配置
                var dbConfig = _configService.GetById(request.DatabaseId);
                if (dbConfig == null)
                {
                    return Ok(ApiResponse<List<CustomerAnalysisResult>>.Fail("数据库配置不存在", 404));
                }

                // 构建连接字符串
                var connectionString = DatabaseTestService.BuildConnectionString(dbConfig);

                var results = new List<CustomerAnalysisResult>();

                using (var connection = new SqlConnection(connectionString))
                {
                    await connection.OpenAsync();

                    // 构建SQL查询语句
                    var sql = @"
                        SELECT 
                            flow_no AS FlowNo,
                            MAX(sale_amount) AS SaleAmount,
                            MAX(oper_date) AS OperDate,
                            COUNT(*) AS PaymentCount,
                            SUM(pay_amount) AS PayAmount
                        FROM t_rm_payflow
                        WHERE oper_date >= @StartDate
                            AND oper_date <= @EndDate
                            AND sale_amount > @MinAmount
                        GROUP BY flow_no
                        ORDER BY MAX(oper_date) DESC";

                    using (var command = new SqlCommand(sql, connection))
                    {
                        // 添加参数
                        command.Parameters.AddWithValue("@StartDate", $"{request.StartDate} 00:00:00");
                        command.Parameters.AddWithValue("@EndDate", $"{request.EndDate} 23:59:59");
                        command.Parameters.AddWithValue("@MinAmount", request.MinAmount);

                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                results.Add(new CustomerAnalysisResult
                                {
                                    FlowNo = reader["FlowNo"].ToString() ?? "",
                                    SaleAmount = reader["SaleAmount"] != DBNull.Value 
                                        ? Convert.ToDecimal(reader["SaleAmount"]) : 0,
                                    OperDate = reader["OperDate"] != DBNull.Value 
                                        ? Convert.ToDateTime(reader["OperDate"]).ToString("yyyy-MM-dd HH:mm:ss") : "",
                                    PaymentCount = reader["PaymentCount"] != DBNull.Value 
                                        ? Convert.ToInt32(reader["PaymentCount"]) : 0,
                                    PayAmount = reader["PayAmount"] != DBNull.Value 
                                        ? Convert.ToDecimal(reader["PayAmount"]) : 0
                                });
                            }
                        }
                    }
                }

                Console.WriteLine($"✓ 客单分析查询成功，共 {results.Count} 条数据");
                return Ok(ApiResponse<List<CustomerAnalysisResult>>.Ok(results, $"查询成功，共 {results.Count} 条数据"));
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"❌ SQL查询失败: {ex.Message}");
                return Ok(ApiResponse<List<CustomerAnalysisResult>>.Fail($"SQL查询失败: {ex.Message}"));
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ 查询异常: {ex.Message}");
                return Ok(ApiResponse<List<CustomerAnalysisResult>>.Fail($"查询异常: {ex.Message}"));
            }
        }
    }
}

